/**
 * Author: Jordi Vilaplana Mayoral <jvilaplana@alumnes.udl.cat>
 * Date: 05/04/2011
 * 
 * LocationManager
 * Copyright (C) 2011 Jordi Vilaplana Mayoral
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 * 
 * @(#)
 * Description: This program allows users to book rooms and schedule activities
 */

package locmanager.persistence;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import locmanager.beans.ActivityBean;
import locmanager.beans.ActivityEquipmentBean;
import locmanager.beans.EquipmentBean;
import locmanager.beans.LocationBean;
import locmanager.beans.RoomTypeBean;
import locmanager.beans.UserBean;

/**
 * Derby implementation of the Persistence Interface.
 * 
 * @author Jordi Vilaplana Mayoral <jvilaplana@alumnes.udl.cat>
 * @version 1.0
 */
public class DerbyPersistence implements Persistence {

  String url = null;
  String driver = null;
  String dbUser = null;
  String dbPassword = null;
  Connection connection = null;

  /**
   * Constructor.
   */
  public DerbyPersistence() {

    Properties props = new Properties();

    try {
      InputStream in = getClass().getResourceAsStream("/locmanager/conf/database.properties");
      props.load(in);
      in.close();
    } catch (IOException ex) {
      Logger.getLogger(DerbyPersistence.class.getName()).log(Level.SEVERE, null, ex);
    }

    url = props.getProperty("url");
    dbUser = props.getProperty("user");
    driver = props.getProperty("driver");
    dbPassword = props.getProperty("password");

  }

  /**
   * Connects to the database with the given parameters.
   * 
   * @param driver
   * @param url
   * @param user
   * @param password 
   */
  public void connect(String driver, String url, String user, String password) {

    try {

      Class.forName(driver).newInstance();
      connection = DriverManager.getConnection(url, user, password);

    } catch (ClassNotFoundException ex) {
      Logger.getLogger(DerbyPersistence.class.getName()).log(Level.SEVERE, null, ex);
    } catch (InstantiationException ex) {
      Logger.getLogger(DerbyPersistence.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IllegalAccessException ex) {
      Logger.getLogger(DerbyPersistence.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
      Logger.getLogger(DerbyPersistence.class.getName()).log(Level.SEVERE, null, ex);
    }

  }

  /**
   * Closes the connection with the database.
   */
  public void disconnect() {
    if(connection != null) {
      try {
        connection.close();
      } catch (SQLException ex) {
      }
    }
  }

  /**
   * Retrieves a location from the database
   * 
   * @param locationId
   * @return LocationBean
   */
  public LocationBean getLocation(int locationId) {
    LocationBean location = null;

    if (connection == null) {
      connect(driver, url, dbUser, dbPassword);
    }

    String query = "SELECT * FROM Locations WHERE LocationId = ?";

    ResultSet resultSet = null;
    PreparedStatement preparedStatement = null;

    try {

      preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, locationId);
      resultSet = preparedStatement.executeQuery();

      if (resultSet.next()) {
        location = new LocationBean(
                resultSet.getInt("locationId"),
                resultSet.getString("publicName"),
                resultSet.getInt("roomTypeId"),
                resultSet.getInt("capacity"));
      }

    } catch (SQLException ex) {
      return location;
    } catch (NullPointerException ex) {
      return location;
    }

    location.setEquipments(getLocationEquipments(location.getLocationId()));

    return location;

  }

  private List<EquipmentBean> getLocationEquipments(int locationId) {

    List<EquipmentBean> equipments = new ArrayList<EquipmentBean>();

    if (connection == null) {
      connect(driver, url, dbUser, dbPassword);
    }

    String query = "SELECT * FROM LocationEquipments WHERE LocationId = ?";

    ResultSet resultSet = null;
    PreparedStatement preparedStatement = null;

    try {

      preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, locationId);
      resultSet = preparedStatement.executeQuery();

      while (resultSet.next()) {
        equipments.add(getEquipment(resultSet.getInt("equipmentId")));
      }

    } catch (SQLException ex) {
      return equipments;
    } catch (NullPointerException ex) {
      return equipments;
    }

    return equipments;

  }

  private EquipmentBean getEquipment(int equipmentId) {

    EquipmentBean equipment = new EquipmentBean();

    if (connection == null) {
      connect(driver, url, dbUser, dbPassword);
    }

    String query = "SELECT * FROM Equipments WHERE EquipmentId = ?";

    ResultSet resultSet = null;
    PreparedStatement preparedStatement = null;

    try {

      preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, equipmentId);
      resultSet = preparedStatement.executeQuery();

      if (resultSet.next()) {
        equipment.setEquipmentId(resultSet.getInt("equipmentId"));
        equipment.setPublicName(resultSet.getString("publicName"));
      }

    } catch (SQLException ex) {
      return equipment;
    } catch (NullPointerException ex) {
      return equipment;
    }

    return equipment;

  }

  public List<LocationBean> getLocations() {
    List<LocationBean> locations = new ArrayList<LocationBean>();

    if (connection == null) {
      connect(driver, url, dbUser, dbPassword);
    }

    String query = "SELECT * FROM Locations";

    ResultSet resultSet = null;
    PreparedStatement preparedStatement = null;

    try {

      preparedStatement = connection.prepareStatement(query);
      resultSet = preparedStatement.executeQuery();

      while (resultSet.next()) {
        locations.add(new LocationBean(
                resultSet.getInt("locationId"),
                resultSet.getString("publicName"),
                resultSet.getInt("roomTypeId"),
                resultSet.getInt("capacity")));
      }

    } catch (SQLException ex) {
      return locations;
    } catch (NullPointerException ex) {
      return locations;
    }

    return locations;
  }

  public boolean insertLocation(String publicName, int roomTypeId, int capacity) {

    if (connection == null) {
      connect(driver, url, dbUser, dbPassword);
    }

    String query = "INSERT INTO Locations (PublicName, RoomTypeId, Capacity) VALUES (?, ?, ?)";
    PreparedStatement preparedStatement = null;

    try {

      preparedStatement = connection.prepareStatement(query);
      preparedStatement.setString(1, publicName);
      preparedStatement.setInt(2, roomTypeId);
      preparedStatement.setInt(3, capacity);
      preparedStatement.executeUpdate();

    } catch (SQLException ex) {
      return false;
    } catch (NullPointerException ex) {
      return false;
    }

    return true;

  }

  public boolean deleteLocation(LocationBean location) {

    if (connection == null) {
      connect(driver, url, dbUser, dbPassword);
    }

    String query = "DELETE FROM Locations WHERE LocationId = ?";
    PreparedStatement preparedStatement = null;

    try {
      preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, location.getLocationId());
      preparedStatement.executeUpdate();
    } catch (Exception ex) {
      return false;
    }

    return true;

  }

  public boolean updateLocation(LocationBean location) {

    if (connection == null) {
      connect(driver, url, dbUser, dbPassword);
    }

    String query = "UPDATE Locations SET PublicName = ?, RoomTypeId = ?, Capacity = ? WHERE locationId = ?";
    PreparedStatement preparedStatement = null;

    try {

      preparedStatement = connection.prepareStatement(query);
      preparedStatement.setString(1, location.getPublicName());
      preparedStatement.setInt(2, location.getRoomTypeId());
      preparedStatement.setInt(3, location.getCapacity());
      preparedStatement.setInt(4, location.getLocationId());
      preparedStatement.executeUpdate();

    } catch (Exception ex) {
      return false;
    }

    query = "DELETE FROM LocationEquipments WHERE LocationId = ?";
    try {

      preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, location.getLocationId());
      preparedStatement.executeUpdate();

    } catch (Exception ex) {
      return false;
    }

    query = "INSERT INTO LocationEquipments (LocationId, EquipmentId) VALUES ";

    boolean first = true;
    for (int i = 0; i < location.getEquipments().size(); i++) {
      if (first) {
        query = query + "(?, ?)";
        first = false;
      } else {
        query = query + ", (?, ?)";
      }
    }

    try {

      preparedStatement = connection.prepareStatement(query);
      int index = 0;
      for (EquipmentBean equipment : location.getEquipments()) {
        preparedStatement.setInt(++index, location.getLocationId());
        preparedStatement.setInt(++index, equipment.getEquipmentId());
      }

      preparedStatement.executeUpdate();

    } catch (Exception ex) {
      return false;
    }

    return true;

  }

  public boolean insertActivity(ActivityBean activity) {

    if (connection == null) {
      connect(driver, url, dbUser, dbPassword);
    }

    String query = "INSERT INTO Activities (PublicName, ManagerId, LocationId, "
            + "Duration, MaxStudents, StartDate) VALUES (?, ?, ?, ?, ?, ?)";
    PreparedStatement preparedStatement = null;

    try {

      preparedStatement = connection.prepareStatement(query);
      preparedStatement.setString(1, activity.getPublicName());
      preparedStatement.setInt(2, activity.getManagerId());
      preparedStatement.setInt(3, activity.getLocationId());
      preparedStatement.setInt(4, activity.getDuration());
      preparedStatement.setInt(5, activity.getMaxStudents());
      preparedStatement.setDate(6, new java.sql.Date(activity.getStartDate().getTime()));
      preparedStatement.executeUpdate();

    } catch (Exception ex) {
      return false;
    }

    ResultSet resultSet;
    int newid = -1;
    try {
      resultSet = preparedStatement.getGeneratedKeys();

      if (resultSet != null && resultSet.next()) {
        newid = resultSet.getInt(1);
      }

    } catch (SQLException ex) {
      Logger.getLogger(DerbyPersistence.class.getName()).log(Level.SEVERE, null, ex);
    }

    query = "INSERT INTO ActivityEquipments (ActivityId, EquipmentId) VALUES ";

    boolean first = true;
    for (int i = 0; i < activity.getEquipments().size(); i++) {
      if (first) {
        query = query + "(?, ?)";
        first = false;
      } else {
        query = query + ", (?, ?)";
      }
    }

    int index = 0;
    for (EquipmentBean equipment : activity.getEquipments()) {
      try {
        preparedStatement.setInt(++index, newid);
        preparedStatement.setInt(++index, equipment.getEquipmentId());
      } catch (SQLException ex) {
      }

    }

    return true;

  }

  public UserBean selectUser(String userName, String password) {

    if (connection == null) {
      connect(driver, url, dbUser, dbPassword);
    }

    String query = "SELECT * FROM Users WHERE UserName = ? AND Password = ?";

    UserBean user = null;
    ResultSet resultSet = null;
    PreparedStatement preparedStatement = null;

    try {

      preparedStatement = connection.prepareStatement(query);
      preparedStatement.setString(1, userName);
      preparedStatement.setString(2, password);
      resultSet = preparedStatement.executeQuery();

      if (resultSet.next()) {

        user = new UserBean();
        user.setUserId(resultSet.getInt("userId"));
        user.setUserName(resultSet.getString("userName"));
        user.setPassword(resultSet.getString("password"));
        user.setUserRole(resultSet.getString("userRole"));

      }

    } catch (SQLException ex) {
      return null;
    } catch (NullPointerException ex) {
      return null;
    }

    return user;

  }

  public List<RoomTypeBean> getRoomTypes() {

    if (connection == null) {
      connect(driver, url, dbUser, dbPassword);
    }

    String query = "SELECT * FROM RoomTypes";

    List<RoomTypeBean> roomTypes = new ArrayList<RoomTypeBean>();
    ResultSet resultSet = null;
    PreparedStatement preparedStatement = null;

    try {

      preparedStatement = connection.prepareStatement(query);
      resultSet = preparedStatement.executeQuery();

      while (resultSet.next()) {
        roomTypes.add(new RoomTypeBean(
                resultSet.getInt("roomTypeId"),
                resultSet.getString("typeName")));
      }

    } catch (SQLException ex) {
      return null;
    } catch (NullPointerException ex) {
      return null;
    }

    return roomTypes;

  }

  public String getRoomType(int roomTypeId) {

    if (connection == null) {
      connect(driver, url, dbUser, dbPassword);
    }

    String query = "SELECT RoomTypeName FROM RoomTypes WHERE RoomTypeId = ?";

    ResultSet resultSet = null;
    PreparedStatement preparedStatement = null;

    try {

      preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, roomTypeId);
      resultSet = preparedStatement.executeQuery();

      if (resultSet.next()) {
        return resultSet.getString("typeName");
      }

    } catch (SQLException ex) {
      return "";
    } catch (NullPointerException ex) {
      return "";
    }

    return "";

  }

  public List<EquipmentBean> getEquipments() {

    if (connection == null) {
      connect(driver, url, dbUser, dbPassword);
    }

    String query = "SELECT * FROM Equipments";

    List<EquipmentBean> equipments = new ArrayList<EquipmentBean>();
    ResultSet resultSet = null;
    PreparedStatement preparedStatement = null;

    try {

      preparedStatement = connection.prepareStatement(query);
      resultSet = preparedStatement.executeQuery();

      while (resultSet.next()) {
        equipments.add(new EquipmentBean(
                resultSet.getInt("equipmentId"),
                resultSet.getString("publicName")));
      }

    } catch (SQLException ex) {
      return null;
    } catch (NullPointerException ex) {
      return null;
    }

    return equipments;

  }

  public List<ActivityBean> getActivitiesByLocation(LocationBean location) {
    if (connection == null) {
      connect(driver, url, dbUser, dbPassword);
    }


    String query = "SELECT * FROM Activies WHERE LocationId = ?";

    List<ActivityBean> activities = new ArrayList<ActivityBean>();
    ResultSet resultSet = null;
    PreparedStatement preparedStatement = null;

    try {

      preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, location.getLocationId());
      resultSet = preparedStatement.executeQuery();

      while (resultSet.next()) {
        activities.add(new ActivityBean(
                resultSet.getInt("ActivityId"),
                resultSet.getString("PublicName"),
                resultSet.getInt("ManagerId"),
                resultSet.getInt("LocationId"),
                resultSet.getInt("Duration"),
                resultSet.getInt("MaxStudents"),
                resultSet.getDate("StartDate"), null));
      }

    } catch (SQLException ex) {
      return null;
    } catch (NullPointerException ex) {
      return null;
    }

    return activities;

  }

  public List<ActivityBean> getActivitiesByLocation(LocationBean location, Date date) {

    if (connection == null) {
      connect(driver, url, dbUser, dbPassword);
    }

    String query = "SELECT * FROM Activities WHERE LocationId = ? AND StartDate = ?";

    List<ActivityBean> activities = new ArrayList<ActivityBean>();
    ResultSet resultSet = null;
    PreparedStatement preparedStatement = null;

    try {

      preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, location.getLocationId());
      preparedStatement.setDate(2, new java.sql.Date(date.getTime()));
      resultSet = preparedStatement.executeQuery();

      while (resultSet.next()) {
        activities.add(new ActivityBean(
                resultSet.getInt("ActivityId"),
                resultSet.getString("PublicName"),
                resultSet.getInt("ManagerId"),
                resultSet.getInt("LocationId"),
                resultSet.getInt("Duration"),
                resultSet.getInt("MaxStudents"),
                resultSet.getDate("StartDate"), null));
      }

    } catch (SQLException ex) {
      return null;
    } catch (NullPointerException ex) {
      return null;
    }

    return activities;

  }

  public EquipmentBean getEquipmentById(int equipmentId) {
    return getEquipment(equipmentId);
  }
  
    public boolean insertRoomType(String typeName) {
    throw new UnsupportedOperationException("Not supported yet.");
  }

  public boolean deleteRoomType(RoomTypeBean roomType) {
    throw new UnsupportedOperationException("Not supported yet.");
  }

  public boolean updateRoomType(RoomTypeBean roomType) {
    throw new UnsupportedOperationException("Not supported yet.");
  }

    public boolean deleteActivity(ActivityBean activity) {
    throw new UnsupportedOperationException("Not supported yet.");
  }

  public boolean updateActivity(ActivityBean activity) {
    throw new UnsupportedOperationException("Not supported yet.");
  }

  public boolean insertEquipment(String publicName) {
    throw new UnsupportedOperationException("Not supported yet.");
  }

  public boolean deleteEquipment(EquipmentBean equipment) {
    throw new UnsupportedOperationException("Not supported yet.");
  }

  public boolean updateEquipment(EquipmentBean equipment) {
    throw new UnsupportedOperationException("Not supported yet.");
  }

  public boolean insertActivityEquipment(int activityId, int equipmentId) {
    throw new UnsupportedOperationException("Not supported yet.");
  }

  public boolean deleteActivityEquipment(ActivityEquipmentBean activityEquipment) {
    throw new UnsupportedOperationException("Not supported yet.");
  }

  public boolean updateActivityEquipment(ActivityEquipmentBean activityEquipment) {
    throw new UnsupportedOperationException("Not supported yet.");
  }

  public boolean insert(String query) {
    throw new UnsupportedOperationException("Not supported yet.");
  }

  public boolean update(String query) {
    throw new UnsupportedOperationException("Not supported yet.");
  }

  public boolean delete(String query) {
    throw new UnsupportedOperationException("Not supported yet.");
  }

  public Object select(String query) {
    throw new UnsupportedOperationException("Not supported yet.");
  }
  
}
